﻿using GFluentCode.Entity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GFluentCode.ORM.SqlServer
{
    /// <summary>
    /// SqlServer系统表操作
    /// </summary>
    public class SqlServerORM: ORMBase
    {
        public SqlServerORM(string conn) : base(conn, EnDbType.SqlServer) { }

        /// <summary>
        /// 查询系统表名
        /// </summary>
        /// <returns></returns>
        public IList<GFluentTableNameEntity> QueryTableNames()
        {
            return QueryBySql<GFluentTableNameEntity>(@"
select a.name as TableName ,
       b.name as SchemaName
From sys.tables a
join sys.schemas b on a.schema_id=b.schema_id
Order by a.name
");
        }

        /// <summary>
        /// 获取表对象
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public GFluentTableEntity QueryTableEntity(string tableName)
        {
            var cols = QueryBySql<GFluentColumnEntity>($@"
SELECT  TableName = d.name,
        ColumnRemark = ISNULL(g.[value], '') ,
        ColumnName = a.name ,
        ColumnType = CASE WHEN b.name IN ( 'varchar', 'nvarchar' )
                  THEN b.name + '('
                       + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))
                       + ')'
                  WHEN b.name = 'decimal'
                  THEN b.name + '('
                       + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))
                       + ','
                       + CAST(COLUMNPROPERTY(a.id, a.name, 'Scale') AS VARCHAR(4))
                       + ')'
                  ELSE b.name
             END
FROM syscolumns a    -- 列名
    LEFT JOIN systypes b ON a.xusertype = b.xusertype    -- 类型    
    INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'    --筛选用户对象    
    LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id    --扩展属性(字段说明)  
WHERE d.name = '{tableName}'    --可修改表名
ORDER BY a.id , a.colorder
");
            return new GFluentTableEntity { TableName = tableName, Columns = cols };
        }

        /// <summary>
        /// 获取表对象
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public IList<GFluentTableEntity> QueryTableEntitys(params string[] tableNames)
        {
            var r = new List<GFluentTableEntity>();

            foreach (var tn in tableNames)
                r.Add(QueryTableEntity(tn));

            return r;
        }
    }
}
